﻿namespace HawaiRestaurant.Models.Query
{
    public class QueryConstants
    {
        public static string LoginQuery = @" DECLARE @Type char(1)
                                             DECLARE @PersonId int
                                             ;with tbl2 as
                                             (
	                                             select * from
	                                             (	
		                                             select u.*,c.CustomerId 'PersonId','c' as 'Type'
		                                             from [User] u join Customer c
			                                             on u.UserName = c.UserName 
			                 
		                                             union
                             
		                                             select u.*,s.StaffId,'s' 
		                                             FROM [User] u join Staff s
			                                             on u.UserName = s.UserName
	                                             ) as tbl
	                                             where tbl.UserName = '{0}' and tbl.[Password] = '{1}'
                                             )
                             
                                             SELECT @Type = [Type],@PersonId = PersonId FROM tbl2
                                             if(@Type = 's')
	                                             select *,'s' as 'Type' from Staff where StaffId = @PersonId
                                             else
	                                             select *,'c' as 'Type' from Customer where CustomerId = @PersonId";

        public static string FavoriteQuery = @" SELECT * FROM 
                                        (SELECT TOP {0}	f.FoodId 'ItemId', f.Name,f.Descriptions,f.[Image],f.FoodUnit 'ItemUnit',
				                                        ft.Name 'ItemTypeName',f.ModifiedDate,f.Favorite, 'Food' as 'Type',StandardPrice
                                        FROM Food f join FoodType ft
                                            ON f.FoodTypeId = ft.FoodTypeId
                                        ORDER BY Favorite DESC) as tblFood
                                        UNION ALL
                                        SELECT * FROM
                                        (SELECT TOP {0}	d.DrinkId 'ItemId',d.Name,d.Descriptions,d.[Image],d.DrinkUnit 'ItemUnit',
				                                        dt.Name 'ItemTypeName',d.ModifiedDate,d.Favorite,'Drink'as 'Type',StandardPrice
                                        FROM Drink d JOIN DrinkType dt
                                            ON d.DrinkTypeId = dt.DrinkTypeId
                                        ORDER BY Favorite DESC) as tblDrink";

        public static string NewQuery = @"SELECT * FROM 
                                        (SELECT TOP {0}	f.FoodId 'ItemId', f.Name,f.Descriptions,f.[Image],f.FoodUnit 'ItemUnit',
				                                        ft.Name 'ItemTypeName',f.ModifiedDate,f.Favorite, 'Food' as 'Type', StandardPrice 
                                        FROM Food f join FoodType ft
                                            ON f.FoodTypeId = ft.FoodTypeId
                                        ORDER BY ModifiedDate DESC) as tblFood
                                        UNION ALL
                                        SELECT * FROM
                                        (SELECT TOP {0}	d.DrinkId 'ItemId',d.Name,d.Descriptions,d.[Image],d.DrinkUnit 'ItemUnit',
				                                        dt.Name 'ItemTypeName',d.ModifiedDate,d.Favorite,'Drink'as 'Type',StandardPrice
                                        FROM Drink d JOIN DrinkType dt
                                            ON d.DrinkTypeId = dt.DrinkTypeId
                                        ORDER BY ModifiedDate DESC) as tblDrink";

        public static string FoodDetailsQuery = @"  SELECT	f.FoodId,
		                                            f.Name,
		                                            f.Descriptions,
		                                            f.[Image],
		                                            f.FoodUnit,
		                                            ft.Name 'FoodTypeName',
		                                            f.ModifiedDate,
		                                            f.Favorite,
                                                    f.StandardPrice
                                                    FROM Food f join FoodType ft
	                                                    ON f.FoodTypeId = ft.FoodTypeId
                                                    WHERE FoodId = {0}";

        public static string DrinkDetailsQuery = @" SELECT	d.DrinkId,
		                                            d.Name,
		                                            d.Descriptions,
		                                            d.[Image],
		                                            d.DrinkUnit,
		                                            dt.Name 'DrinkTypeName',
		                                            d.ModifiedDate,
		                                            d.Favorite,
                                                    d.StandardPrice
                                                    FROM Drink d join DrinkType dt
                                                    ON d.DrinkTypeId = dt.DrinkTypeId
                                                    WHERE DrinkId = {0}";

        public static string CategoryQuery = @" SELECT FoodTypeId 'ItemTypeId',Name,'Food' as 'Type' FROM FoodType
                                                UNION ALL
                                                SELECT DrinkTypeId 'ItemTypeId',Name,'Drink' as 'Type' FROM DrinkType";

        public static string OrderPartyConfirmQuery = @"SELECT	s.Name 'StaffName','Staff' as 'Title',s.PhoneNumber,cus.Name 'CustName',
		                                                    cus.OrganizationName,cus.OrganizationAddress,cus.CustomePhone 'CustPhone',c.PartyDate,
		                                                    c.TotalDeposit,f.Name 'FoodName'
                                                    FROM [Contract] c JOIN Contract_Food cf
	                                                    ON c.ContractId = cf.ContractId JOIN Staff s
	                                                    ON c.StaffId = s.StaffId JOIN Customer cus
	                                                    ON c.CustomerId = cus.CustomerId JOIN Food f
	                                                    ON cf.FoodId = f.FoodId
                                                    WHERE c.ContractId = {0}";

        public static string ReceiptQuery = @"SELECT	r.ReceiptId,d.Name 'ItemName' ,cd.Quantity,d.StandardPrice,
		                                            c.ContractDate,r.ReceiptDate,(c.OlderAmount+c.ChildAmount) as 'NumberOfGuest',s.Name 'StaffName'
                                            FROM Receipt r JOIN [Contract] c
	                                            ON r.ContractId = c.ContractId JOIN Contract_Drink cd
	                                            ON c.ContractId = cd.ContractId JOIN Drink d
	                                            ON cd.DrinkId = d.DrinkId JOIN Staff s
	                                            ON c.StaffId = s.StaffId
                                            UNION ALL
                                            SELECT	r.ReceiptId,f.Name 'ItemName' ,cf.Quantity,f.StandardPrice,
		                                            c.ContractDate,r.ReceiptDate,(c.OlderAmount+c.ChildAmount) as 'NumberOfGuest',s.Name 'StaffName'
                                            FROM Receipt r JOIN [Contract] c
	                                            ON r.ContractId = c.ContractId JOIN Contract_Food cf
	                                            ON c.ContractId = cf.ContractId JOIN Food f
	                                            ON cf.FoodId = f.FoodId JOIN Staff s
	                                            ON c.StaffId = s.StaffId";

        public static string SaleReportQuery = @"SELECT r.ReceiptId,c.ContractTotalAmount,c.Discount,c.TotalDeposit
                                                FROM [Contract] c JOIN Receipt r
	                                                ON c.ContractId = r.ContractId
                                                WHERE CONVERT(DATE,r.ReceiptDate) = CONVERT(DATE,GETDATE())";

        public static string SaleReport2Query = @"SELECT r.ReceiptId,c.ContractTotalAmount,c.Discount,c.TotalDeposit
                                                FROM [Contract] c JOIN Receipt r
	                                                ON c.ContractId = r.ContractId
                                                WHERE CONVERT(DATE,r.ReceiptDate) BETWEEN CONVERT(DATE,{0}) AND CONVERT(DATE,{1})";

        public static string OrderReportQuery = @"SELECT c.ContractId 'OrderId',CONVERT(DATE,c.ContractDate) 'OrderDate',
		                                                cus.CustomerId ,cus.Name 'CustomerName',c.ContractTotalAmount 'OrderValue'
                                                FROM [Contract] c JOIN Customer cus
	                                                ON c.CustomerId = cus.CustomerId
                                                WHERE CONVERT(DATE,c.ContractDate) BETWEEN CONVERT(DATE,{0}) AND CONVERT(DATE,{1})
                                                ORDER BY CONVERT(DATE,c.ContractDate) DESC";

        public static string OrderSummaryQuery = @"SELECT ContractTotalAmount,SUM(NumberOfOrder) 'NumberOfOrder'
                                                    FROM (
                                                    select '{0}' 'ContractTotalAmount',COUNT(ContractId) 'NumberOfOrder'
                                                    from [contract]
                                                    where ContractTotalAmount between 0 and {0}
                                                    group by ContractTotalAmount) as tbl1
                                                    group by ContractTotalAmount

                                                    UNION ALL

                                                    SELECT ContractTotalAmount,SUM(NumberOfOrder) 'NumberOfOrder'
                                                    FROM (
                                                    select '{1}' 'ContractTotalAmount',COUNT(ContractId) 'NumberOfOrder'
                                                    from [contract]
                                                    where ContractTotalAmount between {0} and {1}
                                                    group by ContractTotalAmount) as tbl2
                                                    group by ContractTotalAmount

                                                    UNION ALL

                                                    SELECT ContractTotalAmount,SUM(NumberOfOrder) 'NumberOfOrder'
                                                    FROM (
                                                    select '{2}' 'ContractTotalAmount',COUNT(ContractId) 'NumberOfOrder'
                                                    from [contract]
                                                    where ContractTotalAmount between {1} and {2}
                                                    group by ContractTotalAmount) as tbl3
                                                    group by ContractTotalAmount

                                                    UNION ALL

                                                    SELECT ContractTotalAmount,SUM(NumberOfOrder) 'NumberOfOrder'
                                                    FROM (
                                                    select '{3}' 'ContractTotalAmount',COUNT(ContractId) 'NumberOfOrder'
                                                    from [contract]
                                                    where ContractTotalAmount >={2}
                                                    group by ContractTotalAmount) as tbl3
                                                    group by ContractTotalAmount";
    }
}